import psycopg2

import mysqlDB
from mysqlDB import *
from SqlConfig.sqls import contains_sql
from SqlConfig.sqls import tree_sql


def geneSpatialContainStat(gridLayerName, grid_usage_code, grid_usage_name, gridLayerType, keyField):
    """
    获取网格图层包含部件图层的数量信息
    前提：需将业务库中的tc_region表复制到postgis对应的库中
    :param partLayerName: 部件图层名称
    :param gridLayerName: 网格图层名称
    :return:
    """
    # 建立数据库连接
    pg_con = getConnect()
    # 调用游标对象
    cur = pg_con.cursor()
    # 获取绑定的大小类信息
    subTypeInfo = getSubTypeInfo()
    for row in subTypeInfo:
        phy_layer_id, type_id, type_name, unique_code, phy_layer_name, main_type_name, main_type_code = row
        contains_query_sql = contains_sql.format(
            phy_layer_name,
            gridLayerName,
            grid_usage_name,
            grid_usage_code,
            unique_code,
            type_name,
            main_type_code,
            main_type_name,
            phy_layer_id,
            gridLayerType,
            keyField
        )
        print("正在执行->", gridLayerName, phy_layer_name)
        cur.execute(contains_query_sql)
    print("提交事务")
    pg_con.commit()
    pg_con.close()
    print("执行完成")


def geneSpatialContainStatTree():
    """
    向MySQL.tc_spatial_contain_stat_tree表中插入数据
    :return:
    """
    con = getConnect()
    cur = con.cursor()
    tree_info = getTreeInfo()

    mysql_con = mysqlDB.getConnect()
    mysql_cur = mysql_con.cursor()
    print("1、先清空tc_spatial_contain_stat_tree表数据...")
    mysql_cur.execute("TRUNCATE tc_spatial_contain_stat_tree")
    print("清空tc_spatial_contain_stat_tree成功！")

    print("2、查询图层含有的元素个数，并成成插入语句...")
    values = []
    for info in tree_info:
        layer_name = info[1]
        cur.execute('''
            select count(1) from {}
        '''.format(layer_name))
        num = cur.fetchone()[0]
        info = list(info)
        info.append(num)
        values.append(info)
    print("生成插入语句成功！")

    print("3、开始向MySQL库中的tc_spatial_contain_stat_tree表插入数据，共{0}条记录".format(len(values)))
    mysql_cur.executemany('insert into tc_spatial_contain_stat_tree values (%s, %s, %s, %s, %s, %s,%s, %s)', (values))
    print("4、插入完成，MySQL事务提交...")
    mysql_con.commit()
    print("5、MySQL连接关闭...")
    mysql_con.close()
    print("6、postgis连接关闭...")
    con.commit()
    con.close()


def getConnect():
    """
    建立数据库连接(库名称，用户名，密码，主机ip，端口)根据实际进行修改
    :return:
    """
    pg_con = psycopg2.connect(database="test", user="postgres", password="egova", host="127.0.0.1", port="5432")
    return pg_con


if __name__ == '__main__':
    print("生成tc_spatial_contain_stat")
    # geneSpatialContainStat("北京市", "3029", "市", 1, "cityid")

    # 统计区县 bj_qxq_行政区划为pg中存放的区县图层，根据实际情况修改；distid为区县的关键字段名称，根据实际情况进行修改
    geneSpatialContainStat("qxq", "3012", "区县边界", 2, "distid")

    # 统计社区 bj_sq_社区为pg中存放的街道图层，根据实际情况修改；streetid为街道图层的关键字段名称，根据实际情况进行修改
    # geneSpatialContainStat("bj_jd_街道", "3002", "街道", 3, "streetld")

    # 统计街道 bj_jd_街道为pg中存放的社区图层，根据实际情况修改；comid为社区的关键字段名称，根据实际情况修改
    # geneSpatialContainStat("bj_sq_社区", "3003", "社区", 4, "comld")

    # 统计单元网格 bj_wg_网格为pg中存放的单元网格名称，根据实际情况修改；bgid为单元网格图层的关键字段名称，根据实际情况修改
    # geneSpatialContainStat("bj_wg_网格", "3004", "单元网格", 5, "bgid")

    # print("生成tc_spatial_contain_stat_tree")
    # geneSpatialContainStatTree()
